import pandasql
import sklearn
import numpy as np
import pandas as pd
from pandas import Series, DataFrame
from openpyxl.workbook import Workbook
from pandasql import sqldf, load_meat, load_births

def addone(score):
    return score + 1

if __name__ == '__main__':
    # print("Hello Python!")

    '''
    name = input("What's your name?\n")
    sum = 100 + 100
    print('hello, %s' %name)
    print('sum = %d' %sum)
    '''

    '''
    scoreStr = input("input score=")
    score = int(scoreStr)
    if score >= 90:
        print('Excellent')
    else:
        if score < 60:
            print('Fail')
        else:
            print('Good Job')
    '''

    '''
    sum = 0
    for number in range(11):
        sum = sum + number
    print(sum)
    '''

    '''
    sum = 0
    number = 1
    while number < 11:
        sum = sum + number
        number = number + 1
    print(sum)
    '''

    '''
    lists = ['a', 'b', 'c']
    lists.append('d')
    print(lists)
    print(len(lists))
    lists.insert(0, 'mm')
    lists.pop()
    print(lists)
    '''

    '''
    tuples = ('tupleA', 'tupleB')
    print(tuples[0])
    '''

    '''
    score = {'guanyu':95, 'zhangfei':96}
    score['zhaoyun'] = 98
    print(score)
    score.pop('zhangfei')
    print('guanyu' in score)
    print(score.get('guanyu'))
    print(score.get('yase', 99))
    '''

    '''
    s = set(['a', 'b', 'c'])
    s.add('d')
    s.remove('b')
    print(s)
    print('c' in s)
    '''

    # print(addone(99))

    '''
    while True:
        try:
            line = input()
            a = line.split()
            print(int(a[0]) + int(a[1]))
        except:
            break
    '''

    '''
    sum1 = 0
    for number in range(1, 100, 2):
        sum1 = sum1 + number
    print(sum1)

    print(sum(range(1, 100, 2)))

    i = 1
    s = 0
    while i < 100:
        s = s + i
        i = i + 2
    print(s)
    '''

    '''
    a = np.array([1, 2, 3])
    b = np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]])
    b[1, 1] = 10
    print(a.shape)
    print(b.shape)
    print(a.dtype)
    print(b)
    '''

    '''
    persontype = np.dtype({
        'names': ['name', 'age', 'chinese', 'math', 'english'],
        'formats': ['S32', 'i', 'i', 'i', 'i']
    })
    peoples = np.array([("ZhangFei", 32, 75, 100, 90), ("GuanYu", 24, 85, 96, 88.5), ("ZhaoYun", 28, 85, 92, 96.5),
                        ("HuangZhong", 29, 65, 85, 100)], dtype=persontype)
    ages = peoples[:]['age']
    chineses = peoples[:]['chinese']
    maths = peoples[:]['math']
    englishs = peoples[:]['english']
    print(ages)
    print(np.mean(ages))
    print(np.mean(chineses))
    print(np.mean(maths))
    print(np.mean(englishs))
    '''

    '''
    x1 = np.arange(1, 11, 2)
    x2 = np.linspace(1, 9, 5)
    print(x1)
    print(x2)
    print(np.add(x1, x2))
    print(np.subtract(x1, x2))
    print(np.multiply(x1, x2))
    print(np.divide(x1, x2))
    print(np.power(x1, x2))
    print(np.remainder(x1, x2))
    print(np.mod(x1, x2))
    '''

    '''
    a = np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]])
    print(np.amin(a))
    print(np.amin(a, 0))
    print(np.amin(a, 1))
    print(np.amax(a))
    print(np.amax(a, 0))
    print(np.amax(a, 1))
    '''

    '''
    a = np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]])
    print(np.ptp(a))
    print(np.ptp(a, 0))
    print(np.ptp(a, 1))
    '''

    '''
    a = np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]])
    print(np.percentile(a, 50))
    print(np.percentile(a, 50, axis=0))
    print(np.percentile(a, 50, axis=1))
    '''

    '''
    a = np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]])
    # 求中位数
    print(np.median(a))
    print(np.median(a, axis=0))
    print(np.median(a, axis=1))
    # 求平均数
    print(np.mean(a))
    print(np.mean(a, axis=0))
    print(np.mean(a, axis=1))
    '''

    '''
    a = np.array([1, 2, 3, 4])
    wts = np.array([1, 2, 3, 4])
    print(np.average(a))
    print(np.average(a, weights=wts))
    '''

    '''
    a = np.array([1, 2, 3, 4])
    print(np.std(a)) # 标准差
    print(np.var(a)) # 方差
    '''

    '''
    a = np.array([[4, 3, 2], [2, 4, 1]])
    print(np.sort(a))
    print(np.sort(a, axis=None))
    print(np.sort(a, axis=0))
    print(np.sort(a, axis=1))
    '''

    '''
    scoretype = np.dtype({
        'names': ['name', 'chinese', 'english', 'math', 'total'],
        'formats': ['S32', 'i', 'i', 'i', 'i']
    })
    peoples = np.array([("ZhangFei", 66, 65, 30, 0), ("GuanYu", 95, 85, 98, 0), ("ZhaoYun", 93, 92, 96, 0),
                        ("HuangZhong", 90, 88, 77, 0), ("DianWei", 80, 90, 90, 0)], dtype=scoretype)
    chineses = peoples[:]['chinese']
    englishs = peoples[:]['english']
    maths = peoples[:]['math']

    print('语文成绩: ')
    print('平均成绩 ', np.mean(chineses))
    print('最小成绩 ', np.amin(chineses))
    print('最大成绩 ', np.amax(chineses))
    print('方差 ', np.var(chineses))
    print('标准差 ', np.std(chineses))

    print('-' * 30)
    print('英语成绩: ')
    print('平均成绩 ', np.mean(englishs))
    print('最小成绩 ', np.amin(englishs))
    print('最大成绩 ', np.amax(englishs))
    print('方差 ', np.var(englishs))
    print('标准差 ', np.std(englishs))

    print('-' * 30)
    print('数学成绩: ')
    print('平均成绩 ', np.mean(maths))
    print('最小成绩 ', np.amin(maths))
    print('最大成绩 ', np.amax(maths))
    print('方差 ', np.var(maths))
    print('标准差 ', np.std(maths))

    peoples[:]['total'] = peoples[:]['chinese'] + peoples[:]['english'] + peoples[:]['math']
    print(np.sort(peoples, order='total'))
    '''

    '''
    scoretype = np.dtype({
        'names': ['name', 'chinese', 'english', 'math'],
        'formats': ['S32', 'i', 'i', 'i']
    })
    peoples = np.array([("ZhangFei", 66, 65, 30), ("GuanYu", 95, 85, 98), ("ZhaoYun", 93, 92, 96),
                        ("HuangZhong", 90, 88, 77), ("DianWei", 80, 90, 90)], dtype=scoretype)
    names = peoples[:]['name']
    chineses = peoples[:]['chinese']
    englishs = peoples[:]['english']
    maths = peoples[:]['math']

    # 定义函数用于显示每一排的内容
    def show(name, cj):
        print('{}  |  {}  |  {}  |  {}  |  {}  |  {}  '
              .format(name, np.mean(cj), np.min(cj), np.max(cj), np.var(cj), np.std(cj)))

    print("科目  |  平均成绩  |  最小成绩  |  最大成绩  |  方差  |  标准差")
    show("语文", chineses)
    show("英语", englishs)
    show("数学", maths)

    print("排名: ")
    # 用sorted函数进行排序
    ranking = sorted(peoples, key=lambda x:x[1]+x[2]+x[3], reverse=True)
    print(ranking)
    '''

    '''
    x1 = Series([1, 2, 3, 4])
    x2 = Series(data=[1, 2, 3, 4], index=['a', 'b', 'c', 'd'])
    print(x1)
    print(x2)
    
    d = {'a': 1, 'b': 2, 'c': 3, 'd': 4}
    x3 = Series(d)
    print(x3)
    '''

    '''
    data = {'Chinese': [66, 95, 93, 90, 80], 'English': [65, 85, 92, 88, 90], 'Math': [30, 98, 96, 77, 90]}
    df1 = DataFrame(data)
    df2 = DataFrame(data, index=['ZhangFei', 'GuanYu', 'ZhaoYun', 'HuangZhong', 'DianWei'], columns=['English', 'Math', 'Chinese'])
    print(df1)
    print(df2)
    '''

    '''
    score = DataFrame(pd.read_excel('/Users/wangjianwen/work/pythonProjects/py-test/asset/data.xlsx'))
    score.rename(columns={'Unnamed: 0': 'name'}, inplace=True)
    score.to_excel('/Users/wangjianwen/work/pythonProjects/py-test/asset/data1.xlsx')
    print(score)
    '''

    '''
    data = {'Chinese': [66, 95, 93, 90, 80], 'English': [65, 85, 92, 88, 90], 'Math': [30, 98, 96, 77, 90]}
    df2 = DataFrame(data, index=['ZhangFei', 'GuanYu', 'ZhaoYun', 'HuangZhong', 'DianWei'], columns=['English', 'Math', 'Chinese'])
    print(df2)
    df2 = df2.drop(columns=['Chinese'])
    print(df2)
    df2 = df2.drop(index=['ZhangFei'])
    print(df2)
    df2.rename(columns={'Chinese': 'YuWen', 'English': 'YingYu'}, inplace=True)
    print(df2)
    df2 = df2.drop_duplicates() # 删除重复行
    print(df2)
    df2['Chinese'].astype('str')
    print(df2)
    df2['Chinese'].astype(np.int64)
    print(df2)
    df2['Chinese'] = df2['Chinese'].map(str.strip())
    df2['Chinese'] = df2['Chinese'].map(str.lstrip())
    df2['Chinese'] = df2['Chinese'].map(str.rstrip())
    df2['Chinese'] = df2['Chinese'].str.strip('$')
    df2.columns = df2.columns.str.upper()
    print(df2)
    df2.columns = df2.columns.str.lower()
    print(df2)
    df2.columns = df2.columns.str.title()
    print(df2)
    '''

    '''
    data = {'Chinese': [66, 95, 95, 90, 80], 'English': [65, 85, 92, 88, 90], 'Math': [55, 98, 96, 77, 90]}
    df2 = DataFrame(data, index=['ZhangFei', 'GuanYu', 'ZhaoYun', 'HuangZhong', 'DianWei'],
                    columns=['Chinese', 'English', 'Math'])
    print(df2)
    print(df2.isnull())
    print(df2.isnull().any())
    df2['name'] = df2['name'].apply(str.upper)

    def double_df(x):
        return 2 * x

    df2[u'Chinese'] = df2[u'Chinese'].apply(double_df)
    print(df2)

    def plus(df, n, m):
        df['new1'] = (df[u'Chinese'] + df[u'English']) * m
        df['new2'] = (df[u'Chinese'] + df[u'English']) * n
        return df

    df2 = df2.apply(plus, axis=1, args=(2, 3,))
    print(df2)
    '''

    '''
    df1 = DataFrame({'name': ['ZhangFei', 'GuanYu', 'a', 'b', 'c'], 'data1': range(5)})
    print(df1)
    print(df1.describe())
    '''

    '''
    df1 = DataFrame({'name': ['ZhangFei', 'GuanYu', 'a', 'b', 'c'], 'data1': range(5)})
    df2 = DataFrame({'name': ['ZhangFei', 'GuanYu', 'A', 'B', 'C'], 'data2': range(5)})
    print(df1)
    print(df2)
    df3 = pd.merge(df1, df2, on='name')
    print(df3)
    df4 = pd.merge(df1, df2, how='inner')
    print(df4)
    df5 = pd.merge(df1, df2, how='left')
    print(df5)
    df6 = pd.merge(df1, df2, how='right')
    print(df6)
    df7 = pd.merge(df1, df2, how='outer')
    print(df7)
    '''

    '''
    df1 = DataFrame({'name':['ZhangFei', 'GuanYu', 'a', 'b', 'c'], 'data1':range(5)})
    print(df1)
    pysqldf = lambda sql: sqldf(sql, globals())
    sql = "select * from df1 where name = 'ZhangFei'"
    print(pysqldf(sql))
    '''

    '''
    data = {'语文': [66, 95, 95, 90, 80, 80], '英语': [65, 85, 92, 88, 90, 90], '数学': [np.nan, 98, 96, 77, 90, 90]}
    df = DataFrame(data, index=['张飞', '关羽', '赵云', '黄忠', '典韦', '典韦'],
                   columns=['语文', '英语', '数学'])
    print(df)
    df = df.drop_duplicates() # 去重
    print(df)
    df = df.fillna(df['数学'].mean()) # 替换NaN值
    print(df)
    df['sum'] = [df.loc[name].sum() for name in df.index] # 增加一行统计
    print(df)
    df = df.sort_values(by='sum', ascending=False)
    print(df)
    '''
